package com.eorchis.module.trainingneeds.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.dao.IDaoSupport.QueryStringType;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.module.trainingneeds.dao.IQuestionnaireDao;
import com.eorchis.module.trainingneeds.domain.QueryQuestionBankBean;
import com.eorchis.module.trainingneeds.domain.QueryQuestionnaireBean;
import com.eorchis.module.trainingneeds.domain.QuerySurveyObjectBean;
import com.eorchis.module.trainingneeds.domain.QuestionBank;
import com.eorchis.module.trainingneeds.domain.Questionnaire;
import com.eorchis.module.trainingneeds.domain.QuestionnaireSurveyObject;

import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.basedao.query.mapper.MapperType;
import com.eorchis.core.basedao.query.mapper.ResultMapperFactory;
import com.eorchis.core.basedao.query.mapper.impl.BeanResultMapper;

import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;

import com.eorchis.module.trainingneeds.ui.commond.QuestionBankQueryCommond;
import com.eorchis.module.trainingneeds.ui.commond.QuestionBankValidCommond;
import com.eorchis.module.trainingneeds.ui.commond.QuestionnaireQueryCommond;
import com.eorchis.module.trainingneeds.ui.commond.QuestionnaireSurveyObjectQueryCommond;
import com.eorchis.module.trainingneeds.ui.commond.QuestionnaireSurveyObjectValidCommond;
import com.eorchis.module.trainingneeds.ui.commond.QuestionnaireValidCommond;
import com.eorchis.module.util.SqlQueryUtil;
import com.eorchis.utils.DateUtil;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: QuestionnaireDaoImpl<br>
 * Description: 培训需求问卷表数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author HuangTF
 * @createDate 2014-02-07
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.trainingneeds.dao.impl.QuestionnaireDaoImpl")
public class QuestionnaireDaoImpl extends HibernateAbstractBaseDao implements IQuestionnaireDao {
	
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Class<? extends IBaseEntity> entityClass() {
		return Questionnaire.class;
	}

	@Override
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		QuestionnaireQueryCommond questionnaireQueryCommond = (QuestionnaireQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT q.QUESTIONNAIRE_ID as questionnaireId," +
				" q.QUESTIONNAIRE_NAME as questionnaireName,bb.DATA_NAME as questionnaireType,q.BEGIN_DATE as beginDate," +
				" q.END_DATE as endDate,bu.user_name as userName,bd.DEPNAME as depName,q.PUBLISH_STATE as publishState," +
				" q.SURVEY_TYPE as surveyType,q.SURVEY_OBJECT_TYPE as surveyObjectType" +
				" FROM questionnaire q left join base_user bu on q.USERID=bu.USERID" +
				" left join base_department bd on q.DEPID=bd.DEPID " +
				" left join base_basedata bb on q.QUESTIONNAIRE_TYPE=bb.DATA_CODE");
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getSearchQuestionnaireId()))
		{
			defaultQueryConditionBuilder.addCondition("q.QUESTIONNAIRE_ID", CompareType.EQUAL, questionnaireQueryCommond.getSearchQuestionnaireId());
		}
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getActiveState()))
		{
			defaultQueryConditionBuilder.addCondition("q.ACTIVE_STATE", CompareType.EQUAL, questionnaireQueryCommond.getActiveState());
		}
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getSearchQuestionnaireName()))
		{
			defaultQueryConditionBuilder.addCondition("q.QUESTIONNAIRE_NAME", CompareType.LIKE, questionnaireQueryCommond.getSearchQuestionnaireName().trim());
		}
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getSearchQuestionnaireType()))
		{
			defaultQueryConditionBuilder.addCondition("q.QUESTIONNAIRE_TYPE", CompareType.EQUAL, questionnaireQueryCommond.getSearchQuestionnaireType());
		}
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getPublishState()))
		{
			defaultQueryConditionBuilder.addCondition("q.PUBLISH_STATE", CompareType.EQUAL, questionnaireQueryCommond.getPublishState());
		}
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getSearchBeginDate()))
		{
			defaultQueryConditionBuilder.addCondition("q.BEGIN_DATE", CompareType.LIKE, questionnaireQueryCommond.getSearchBeginDate());
		}
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getSearchEndDate()))
		{
			defaultQueryConditionBuilder.addCondition("q.END_DATE", CompareType.LIKE, questionnaireQueryCommond.getSearchEndDate());
		}
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getUserName()))
		{
			String userName=SqlQueryUtil.changeString(questionnaireQueryCommond.getUserName().replace(" ", ""));
			defaultQueryConditionBuilder.addCondition("bu.user_name", CompareType.LIKE,userName);
		}
		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getDeptName()))
		{
			defaultQueryConditionBuilder.addCondition("bd.DEPNAME", CompareType.LIKE,questionnaireQueryCommond.getDeptName().trim());
		}
//		if(PropertyUtil.objectNotEmpty(questionnaireQueryCommond.getSortInfos()))
//			defaultQueryConditionBuilder.addSort(questionnaireQueryCommond.getSortInfos());
//		else
//			defaultQueryConditionBuilder.addSort("q.CREATE_DATE", OrderType.DESC);
			
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setResultObject(QueryQuestionnaireBean.class);
	}
	/**
	 * 判断培训需求调查问卷名称是否重复
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-7
	 */
	@Override
	public boolean isRepeatName(String questionnaireName) throws Exception
	{
		String sql="select * from questionnaire q where q.QUESTIONNAIRE_NAME = :questionnaireName";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireName", questionnaireName);
		List<?> list=this.executeFind(QueryStringType.SQL, sql, params);
		if(list.size()>0)
		{
			return true;
		}
		
		return false;
	}
	/**
	 * 发布撤销培训需求调查问卷
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-7
	 */
	@Override
	public int publishQuestionnaire(QuestionnaireQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("update questionnaire q set q.PUBLISH_STATE = :publishState where q.QUESTIONNAIRE_ID in (:questionnaireIds)");
		params.put("publishState", queryCommond.getPublishState());
		params.put("questionnaireIds", queryCommond.getSearchQuestionnaireIds());
		
		return this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}
	/**
	 * 判断是否可以发布培训需求调查问卷
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-7
	 */
	@Override
	public boolean isPublish(QuestionnaireQueryCommond queryCommond) throws Exception
	{
		Map<String, Object> params=new HashMap<String, Object>();
		String[] questionnaireIds=queryCommond.getSearchQuestionnaireIds();
		for(String id: questionnaireIds)
		{
			params.put("questionnaireId", id);
			String[] tables={"questionnaire_question","questionnaire_survey_object"};
			
			for(String table: tables)
			{
				String sql="select count(*) from "+table+" t where t.QUESTIONNAIRE_ID = :questionnaireId";
				List<BigInteger> count=(List<BigInteger>) this.executeFind(QueryStringType.SQL, sql, params);
				if(count.get(0).intValue()==0)
				{
					return false;
				}
			}
		}
		return true;
	}
	/**
	 * 删除培训需求调查问卷时，删除关联表数据
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-7
	 */
	@Override
	public void delAll(QuestionnaireQueryCommond queryCommond) throws Exception
	{
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireIds", queryCommond.getSearchQuestionnaireIds());
		
		String[] tables={"questionnaire_class","questionnaire_question","questionnaire_question_type"};
		for(String table: tables)
		{
			String sql="delete from "+table+" where QUESTIONNAIRE_ID in (:questionnaireIds)";
			this.executeUpdate(QueryStringType.SQL, sql, params);
		}
	}
//	/**
//	 * 更新问卷调查对象类型
//	 * @param queryCommond
//	 * @return
//	 * @throws Exception
//	 * @author HuangTF
//	 * @createDate 2014-5-27
//	 */
//	@Override
//	public void updateSurveyObjectType(QuestionnaireSurveyObjectQueryCommond queryCommond) throws Exception
//	{
//		String sql="update questionnaire_survey_object qso set qso.SURVEY_OBJECT_TYPE="+queryCommond.getSurveyObjectType()+
//				" where qso.QUESTIONNAIRE_ID='"+queryCommond.getQuestionnaireId()+"'";
//		this.executeUpdate(QueryStringType.SQL, sql, null);
//	}
	/**
	 * 更新问卷表调查对象类型
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-27
	 */
	@Override
	public void updateSurveyObjectTypeQuestionnaire(QuestionnaireSurveyObjectQueryCommond queryCommond) throws Exception
	{
		String sql="update questionnaire q set q.SURVEY_OBJECT_TYPE="+queryCommond.getSurveyObjectType()+" " +
				" where q.QUESTIONNAIRE_ID='"+queryCommond.getQuestionnaireId()+"'";
		this.executeUpdate(QueryStringType.SQL, sql, null);
	}
	/**
	 * 删除问卷调查对象类型信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-27
	 */
	@Override
	public void delSurveyObjectType(QuestionnaireSurveyObjectQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		if(queryCommond.getSurveyObjectType().intValue()==QuestionnaireSurveyObject.SURVEY_TYPE_ALL)
		{
			sql.append("delete from questionnaire_survey_object where questionnaire_survey_object.QUESTIONNAIRE_ID='"+queryCommond.getQuestionnaireId()+"'");
		}
		else
			if(queryCommond.getSurveyObjectType().intValue()==QuestionnaireSurveyObject.SURVEY_TYPE_USER)
			{
				sql.append("delete from questionnaire_survey_object where questionnaire_survey_object.QUESTIONNAIRE_ID='"+queryCommond.getQuestionnaireId()+"' " +
						" and questionnaire_survey_object.SURVEY_OBJECT_ID is NULL");
			}
		this.executeUpdate(QueryStringType.SQL, sql.toString(), null);
	}
	/**
	 * 查询已选调查对象(指定人员)
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-27
	 */
	@Override
	public List<QuerySurveyObjectBean> querySurveyObjectSelected(QuestionnaireSurveyObjectQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		sql.append("select * from (");
		sql.append("select qso.QUESTIONNAIRE_SURVEY_OBJECT_ID as questionnaireSurveyObjectId,bu.USERID as userId," +
				" bu.user_name as userName,bb_1.DATA_NAME as sex,bu.birthday as birthday,bd.DEPNAME as depName," +
				" bu.DUTY_REMARK as position,bb_2.DATA_NAME as rank,bu.login_id as loginId " +
				" FROM questionnaire_survey_object qso " +
				" INNER JOIN base_user bu ON qso.SURVEY_OBJECT_ID=bu.USERID" +
				" LEFT JOIN base_department_user bdu on bu.USERID=bdu.USERID" +
				" LEFT JOIN base_department bd on bdu.DEPID=bd.DEPID" +
				" LEFT JOIN base_basedata bb_1 on bu.sex_code=bb_1.DATA_CODE" +
				" LEFT JOIN base_basedata bb_2 on bu.DUTY_LEVEL_CODE=bb_2.DATA_CODE" +
				" where qso.QUESTIONNAIRE_ID = :questionnaireId");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getQuestionnaireId());
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSurveyObjectType()))
		{
			sql.append(" and qso.SURVEY_OBJECT_TYPE = :surveyObjectType");
			params.put("surveyObjectType", queryCommond.getSurveyObjectType());
		}
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getUserName()))
		{
			String userName=SqlQueryUtil.changeString(queryCommond.getUserName().replace(" ", ""));
			sql.append(" and bu.user_name like :userName");
			params.put("userName", userName);
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getLoginId()))
		{
			sql.append(" and bu.login_id = :loginId");
			params.put("loginId", queryCommond.getLoginId().trim());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getDepName()))
		{
			sql.append(" and bd.DEPNAME like :depName");
			params.put("depName", "%"+queryCommond.getDepName().trim()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getRankCode()))
		{
			sql.append(" and bu.DUTY_LEVEL_CODE = :rankCode");
			params.put("rankCode", queryCommond.getRankCode());
		}
		sql.append(") as t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} 
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QuerySurveyObjectBean> findList = new ArrayList<QuerySurveyObjectBean>();
		
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QuerySurveyObjectBean.class);
		queryCondition.setResultMapper(mapper);
		
		if(queryCommond.getPage()==-1)
		{
			findList = (List<QuerySurveyObjectBean>) this.findList(queryCondition);
		}
		else
		{
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QuerySurveyObjectBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		
		return findList;
	}
	/**
	 * 查询未选调查对象(指定人员)
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-27
	 */
	@Override
	public List<QuerySurveyObjectBean> querySurveyObjectNoSelected(QuestionnaireSurveyObjectQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		sql.append("select * from (");
		sql.append("select bu.USERID as userId,bu.user_name as userName,bb_1.DATA_NAME as sex,bu.birthday as birthday," +
				" bd.DEPNAME as depName,bu.DUTY_REMARK as position,bb_2.DATA_NAME as rank,bu.login_id as loginId " +
				" from base_user bu " +
				" left join base_department_user bdu on bu.USERID=bdu.USERID" +
				" left join base_department bd on bdu.DEPID=bd.DEPID" +
				" left join base_basedata bb_1 on bu.sex_code=bb_1.DATA_CODE" +
				" left join base_basedata bb_2 on bu.DUTY_LEVEL_CODE=bb_2.DATA_CODE" +
				" where bd.TREEPATH like :treePath and bu.USERID not in " +
				" (select qso.SURVEY_OBJECT_ID from questionnaire_survey_object qso " +
				" where qso.QUESTIONNAIRE_ID = :questionnaireId and qso.SURVEY_OBJECT_TYPE = :surveyObjectType)");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getQuestionnaireId());
		params.put("treePath", queryCommond.getTreePath()+"%");
		params.put("surveyObjectType", QuestionnaireSurveyObject.SURVEY_TYPE_USER);
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getUserName()))
		{
			String userName=SqlQueryUtil.changeString(queryCommond.getUserName().replace(" ", ""));
			sql.append(" and bu.user_name like :userName");
			params.put("userName", userName);
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getLoginId()))
		{
			sql.append(" and bu.login_id = :loginId");
			params.put("loginId", queryCommond.getLoginId().trim());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getDepName()))
		{
			sql.append(" and bd.DEPNAME like :depName");
			params.put("depName", "%"+queryCommond.getDepName().trim()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getRankCode()))
		{
			sql.append(" and bu.DUTY_LEVEL_CODE = :rankCode");
			params.put("rankCode", queryCommond.getRankCode());
		}
		sql.append(") as t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} 
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QuerySurveyObjectBean> findList = new ArrayList<QuerySurveyObjectBean>();
		
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QuerySurveyObjectBean.class);
		queryCondition.setResultMapper(mapper);
		
		if(queryCommond.getPage()==-1)
		{
			findList = (List<QuerySurveyObjectBean>) this.findList(queryCondition);
		}
		else
		{
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QuerySurveyObjectBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		
		return findList;
	}
	
	/**
	 * 查询题库试题列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-17
	 */
	@Override
	public List<QueryQuestionBankBean> queryQuestionBank(QuestionBankQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		sql.append("select t.questionId as \"questionId\", t.questionName as \"questionName\", t.questionType as \"questionType\"," +
				" t.createDate as \"createDate\", t.isSelectedBig as \"isSelected\"," +//t.userName as \"userName\", 
				" t.questionsProblemSets as \"questionsProblemSets\" from (");
		sql.append("select q.QUESTION_ID as questionId,q.QUESTION_NAME as questionName,bb.DATA_NAME as questionType," +
				" q.CREATE_DATE as createDate,q.QUESTIONS_PROBLEM_SETS as questionsProblemSets, " +//bu.user_name as userName,
				"IFNULL((SELECT max(qq.ACTIVE_STATE) FROM questionnaire_question qq WHERE qq.QUESTION_ID = q.QUESTION_ID),3) as isSelectedBig" +
				" from question_bank q " +
				" left join base_basedata bb on bb.DATA_CODE=q.QUESTION_TYPE" +
//				" left join base_user bu on bu.USERID=q.USERID" +
				" where q.ACTIVE_STATE = :activeState");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("activeState", queryCommond.getActiveState());
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionId()))
		{
			sql.append(" and q.QUESTION_ID = :questionId");
			params.put("questionId", queryCommond.getQuestionId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionName()))
		{
			sql.append(" and q.QUESTION_NAME like :questionName");
			params.put("questionName", "%"+queryCommond.getQuestionName().trim()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionType()))
		{
			sql.append(" and q.QUESTION_TYPE = :questionType");
			params.put("questionType", queryCommond.getQuestionType());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getCreateDateStart()))
		{
			sql.append(" and to_char(q.create_date,'yyyy-mm-dd') between :dateStart and :dateEnd");
			params.put("dateStart", queryCommond.getCreateDateStart());
			params.put("dateEnd", queryCommond.getCreateDateEnd());
		}
		/*if(PropertyUtil.objectNotEmpty(queryCommond.getCreaterName()))
		{
			String userName=SqlQueryUtil.changeString(queryCommond.getCreaterName().replace(" ", ""));
			sql.append(" and bu.user_name like :userName");
			params.put("userName", userName);
		}*/
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionsProblemSets()))
		{
			String questionsProblemSets = SqlQueryUtil.changeString(queryCommond.getQuestionsProblemSets().replace(" ", ""));
			sql.append(" and bu.questions_problem_sets like :questionsProblemSets");
			params.put("questionsProblemSets", questionsProblemSets);
		}
		sql.append(") t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		else
		{
			sql.append(" order by t.createDate desc");
		}
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QueryQuestionBankBean> findList = new ArrayList<QueryQuestionBankBean>();
		
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryQuestionBankBean.class);
		queryCondition.setResultMapper(mapper);
		
		if(queryCommond.getPage()==-1)
		{
			findList = (List<QueryQuestionBankBean>) this.findList(queryCondition);
		}
		else
		{
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryQuestionBankBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		
		return findList;
	}
	/**
	 * 查询试题信息
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-17
	 */
	@Override
	public QueryQuestionBankBean queryQuestionInfo(QuestionBankValidCommond commond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("select qb.QUESTION_ID as \"questionId\",qb.QUESTION_NAME as \"questionName\"," +
				" qb.QUESTION_TYPE as \"questionType\",qb.WORD_NUM as \"wordNum\"," +
				" GROUP_CONCAT(concat(qbo.OPTION_NUM,'-',ifnull(qbo.objective_topic,2),'-',qbo.OPTION_NAME)) as \"questionOptionStr\"," +
				" qb.IS_SCORE_TYPE as \"isScoreType\", qb.QUESTIONS_PROBLEM_SETS as \"questionsProblemSets\"" +
				" from question_bank qb " +
				" left join question_bank_option qbo on qb.QUESTION_ID=qbo.QUESTION_ID" +
				" where qb.QUESTION_ID = :questionId" +
				" GROUP BY qb.QUESTION_ID,qb.QUESTION_NAME,qb.QUESTION_TYPE,qb.WORD_NUM,qb.IS_SCORE_TYPE,qb.QUESTIONS_PROBLEM_SETS");
		params.put("questionId", commond.getQuestionId());
		IConditionBuilder defaultQueryConditionBuilder=new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryQuestionBankBean.class);
		queryCondition.setResultMapper(mapper);
		
		List<QueryQuestionBankBean> resultList=(List<QueryQuestionBankBean>) this.findList(queryCondition);
		if(resultList.size()>0)
		{
			return resultList.get(0);
		}
		return null;
	}
	/**
	 * 判断试题题干是否重复
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-18
	 */
	@Override
	public boolean isRepeatQuestionName(QuestionBankValidCommond commond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		sql.append("select * from question_bank qb where qb.QUESTION_NAME = :questionName and qb.QUESTION_TYPE = :questionType and qb.ACTIVE_STATE="+QuestionBank.IS_ACTIVE_Y);
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionName", commond.getQuestionName());
		params.put("questionType", commond.getQuestionType());
		if(PropertyUtil.objectNotEmpty(commond.getQuestionId()))
		{
			sql.append(" and qb.QUESTION_ID <> :questionId");
			params.put("questionId", commond.getQuestionId());
		}
		List<?> list=this.executeFind(QueryStringType.SQL, sql.toString(), params);
		if(list.size()==0)
		{
			return false;
		}
		else
		{
			return true;
		}
	}
	/**
	 * 更新题库试题
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-17
	 */
	@Override
	public void updateQuestionBank(QuestionBankValidCommond commond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		
		sql.append("update question_bank qb set qb.QUESTION_ID=qb.QUESTION_ID");
		
		if(PropertyUtil.objectNotEmpty(commond.getQuestionName()))
		{
			sql.append(",qb.QUESTION_NAME = :questionName");
			params.put("questionName", commond.getQuestionName());
		}
		if(PropertyUtil.objectNotEmpty(commond.getQuestionType()))
		{
			sql.append(",qb.QUESTION_TYPE = :questionType");
			params.put("questionType", commond.getQuestionType());
		}
		if(PropertyUtil.objectNotEmpty(commond.getQuestionTypes()))
		{
			sql.append(",qb.IS_SCORE_TYPE = :questionTypes");
			params.put("questionTypes", commond.getQuestionTypes());
		}
		if(PropertyUtil.objectNotEmpty(commond.getWordNum()))
		{
			sql.append(",qb.WORD_NUM = :wordNum");
			params.put("wordNum", commond.getWordNum());
		}
		else
		{
			sql.append(",qb.WORD_NUM = null");
		}
		
		sql.append(" where qb.QUESTION_ID = :questionId");
		params.put("questionId", commond.getQuestionId());
		
		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}
	/**
	 * 更新试题启用作废状态
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-17
	 */
	@Override
	public void updateQuestionActiveState(QuestionBankQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		
		sql.append("update question_bank qb set qb.ACTIVE_STATE = :activeState");
		sql.append(" where qb.QUESTION_ID in (:questionIds)");
		
		params.put("questionIds", queryCommond.getQuestionIds());
		params.put("activeState", queryCommond.getActiveState());
		
		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}
	/**
	 * 删除试题选项
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-17
	 */
	@Override
	public void delQuestionOption(QuestionBankValidCommond commond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		sql.append("delete from question_bank_option where question_bank_option.QUESTION_ID='"+commond.getQuestionId()+"'");
		
		this.executeUpdate(QueryStringType.SQL, sql.toString(), null);
	}
//	/**
//	 * 判断题库试题是否可以删除
//	 * @param queryCommond
//	 * @return
//	 * @throws Exception
//	 * @author HuangTF
//	 * @createDate 2014-2-17
//	 */
//	public boolean isCanDel(String[] ids) throws Exception
//	{
//		String sql="SELECT q.PUBLISH_STATE FROM questionnaire q " +
//				" LEFT JOIN questionnaire_question qq on q.QUESTIONNAIRE_ID=qq.QUESTIONNAIRE_ID " +
//				" LEFT JOIN question_bank qb on qq.QUESTION_ID=qb.QUESTION_ID " +
//				" WHERE qb.QUESTION_ID in (:ids) GROUP BY q.PUBLISH_STATE";
//		Map<String, Object> params=new HashMap<String, Object>();
//		
//		params.put("ids", ids);
//		
//		List<Integer> list=(List<Integer>) this.executeFind(QueryStringType.SQL, sql, params);
//		
//		for(Integer i: list)
//		{
//			if(i.intValue()==Questionnaire.IS_PUBLISH_Y.intValue())
//			{
//				return false;
//			}
//		}
//		
//		return true;
//	}
	/**
	 * 查询已发布问卷的试题，用于前台判断题库试题是否可以删除
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-17
	 */
	@Override
	public List<String> isCanDel(QuestionBankQueryCommond queryCommond) throws Exception
	{
		String sql="SELECT qq.QUESTION_ID FROM questionnaire_question qq WHERE qq.QUESTIONNAIRE_ID IN" +
				" (SELECT q.QUESTIONNAIRE_ID from questionnaire q where q.PUBLISH_STATE="+Questionnaire.IS_PUBLISH_Y+") " +
				" GROUP BY qq.QUESTION_ID";
		
		List<String> list=(List<String>) this.executeFind(QueryStringType.SQL, sql, null);
		
		return list;
	}
	/**
	 * 查询全部题型
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-18
	 */
	@Override
	public List<String> queryAllQuestionType() throws Exception
	{
		String sql="select bb.DATA_CODE from base_basedata bb where bb.TYPE_ID = :typeId";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("typeId", "402863814443d59d014443d971c20004");
		
		List<String> listType=(List<String>) this.executeFind(QueryStringType.SQL, sql, params);
		
		return listType;
	}
	
	/**
	 * 查询问卷表已设置题型
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-18
	 */
	@Override
	public List<String> queryQuestionType(QuestionBankQueryCommond queryCommond) throws Exception
	{
		String sql="select qqt.QUESTION_TYPE from questionnaire_question_type qqt where qqt.QUESTIONNAIRE_ID = :questionnaireId";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getQuestionnaireId());
		
		List<String> listType=(List<String>) this.executeFind(QueryStringType.SQL, sql, params);
		
		return listType;
	}
	/**
	 * 问卷设置试题时查询未选题库试题
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-18
	 */
	@Override
	public List<QueryQuestionBankBean> queryQuestionNoSelected(QuestionBankQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		sql.append("select t.questionId as \"questionId\", t.questionName as \"questionName\", t.questionType as \"questionType\"" +
				" from (select qb.QUESTION_ID as questionId,qb.QUESTION_NAME as questionName,bb.DATA_NAME as questionType" +
				" from question_bank qb left join base_basedata bb on qb.QUESTION_TYPE = bb.DATA_CODE" +
				" where qb.ACTIVE_STATE = :activeState");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("activeState", queryCommond.getActiveState());
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionName()))
		{
			sql.append(" and qb.QUESTION_NAME like :questionName");
			params.put("questionName", "%"+queryCommond.getQuestionName().trim()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionType()))
		{
			sql.append(" and qb.QUESTION_TYPE = :questionType");
			params.put("questionType", queryCommond.getQuestionType());
		}
		
		sql.append(" and qb.QUESTION_ID not in" +
				" (select qq.QUESTION_ID from questionnaire_question qq where qq.QUESTIONNAIRE_ID = :questionnaireId) ) t");
		params.put("questionnaireId", queryCommond.getQuestionnaireId());
		
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QueryQuestionBankBean> findList = new ArrayList<QueryQuestionBankBean>();
		
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryQuestionBankBean.class);
		queryCondition.setResultMapper(mapper);
		
		if(queryCommond.getPage()==-1)
		{
			findList = (List<QueryQuestionBankBean>) this.findList(queryCondition);
		}
		else
		{
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryQuestionBankBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		
		return findList;
	}
	/**
	 * 问卷设置试题时查询已选题库试题
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-18
	 */
	@Override
	public List<QueryQuestionBankBean> queryQuestionSelected(QuestionBankQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("select t.questionnaireQuestionId as \"questionnaireQuestionId\", t.questionId as \"questionId\"," +
				" t.questionName as \"questionName\", t.isMust as \"isMust\", t.orderNum as \"orderNum\" from (");
		sql.append("select qq.QUESTIONNAIRE_QUESTION_ID as questionnaireQuestionId,qq.QUESTION_ID as questionId," +
				" qb.QUESTION_NAME as questionName,qq.IS_MUST as isMust,qq.ORDER_NUM as orderNum" +
				" from questionnaire_question qq" +
				" left join question_bank qb on qq.QUESTION_ID=qb.QUESTION_ID" +
				" where qq.QUESTIONNAIRE_ID = :questionnaireId");
		params.put("questionnaireId", queryCommond.getQuestionnaireId());
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionType()))
		{
			sql.append(" and qb.QUESTION_TYPE = :questionType");
			params.put("questionType", queryCommond.getQuestionType());
		}
		sql.append(" ) t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		else
		{
			sql.append(" order by t.orderNum asc");
		}
		
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setResultObject(QueryQuestionBankBean.class);
		
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		
		Long count = this.count(queryCondition);
		List<QueryQuestionBankBean> findList = new ArrayList<QueryQuestionBankBean>();
		
		if(queryCommond.getPage()==-1)
		{
			findList = (List<QueryQuestionBankBean>) this.findList(queryCondition);
		}
		else
		{
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryQuestionBankBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		
		return findList;
	}
	/**
	 * 新增问卷试题时，查询已选问卷试题的最大排序序号
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-18
	 */
	@Override
	public int queryQuestionnaireQuestionMaxOrder(QuestionBankQueryCommond queryCommond) throws Exception
	{
		String sql="select max(qq.ORDER_NUM)" +
				" from questionnaire_question qq" +
				" left join question_bank qb on qq.QUESTION_ID=qb.QUESTION_ID" +
				" where qq.QUESTIONNAIRE_ID = '"+queryCommond.getQuestionnaireId()+"'" +
				" and qb.QUESTION_TYPE='"+queryCommond.getQuestionType()+"'";
		List<BigDecimal> orderNum=(List<BigDecimal>) this.executeFind(QueryStringType.SQL, sql, null);
		
		if(orderNum.size()>0&&PropertyUtil.objectNotEmpty(orderNum.get(0)))
			return orderNum.get(0).intValue();
		else
			return 0;
	}
	/**
	 * 问卷查看列表,查看已结束问卷调查情况
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-4-29
	 */
	@Override
	public List<QueryQuestionnaireBean> findEndQuestionnaireList(QuestionnaireQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("select * from (");
		sql.append("SELECT q.QUESTIONNAIRE_ID as questionnaireId," +
				" q.QUESTIONNAIRE_NAME as questionnaireName,q.QUESTIONNAIRE_TYPE as questionnaireType,q.BEGIN_DATE as beginDate," +
				" q.END_DATE as endDate,bu.user_name as userName,bd.DEPNAME as depName," +
				" (select count(*) from questionnaire_survey_object qso where qso.QUESTIONNAIRE_ID=q.QUESTIONNAIRE_ID) as surveyUserNumBig" +
				" FROM questionnaire q " +
				" left join base_user bu on q.USERID=bu.USERID" +
				" left join base_department bd on q.DEPID=bd.DEPID where 1=1");
		sql.append(" and q.END_DATE<"+"'"+DateUtil.getDateString(new Date(), "yyyy-MM-dd HH:mm:ss")+"'");//只查询已结束的问卷
		if(PropertyUtil.objectNotEmpty(queryCommond.getActiveState()))
		{
			sql.append(" and q.ACTIVE_STATE = :activeState");
			params.put("activeState", queryCommond.getActiveState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireName()))
		{
			sql.append(" and q.QUESTIONNAIRE_NAME like :questionnaireName");
			params.put("questionnaireName", "%"+queryCommond.getSearchQuestionnaireName().trim()+"%");
		}
		
		
		
		
		
		//需要确认开始时间和结束时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate()))
		{
			sql.append(" and q.BEGIN_DATE like :beginDate");
			params.put("beginDate", "%"+queryCommond.getSearchBeginDate()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate()))
		{
			sql.append(" and q.END_DATE like :endDate");
			params.put("endDate", "%"+queryCommond.getSearchEndDate()+"%");
		}
		
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getUserName()))
		{
			String userName=SqlQueryUtil.changeString(queryCommond.getUserName().replace(" ", ""));
			sql.append(" and bu.user_name like :userName");
			params.put("userName", userName);
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getDeptName()))
		{
			sql.append(" and bd.DEPNAME like :depName");
			params.put("depName", "%"+queryCommond.getDeptName().trim()+"%");
		}
		sql.append(") as t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QueryQuestionnaireBean> questionnaireBeanList=new ArrayList<QueryQuestionnaireBean>();
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryQuestionnaireBean.class);
		queryCondition.setResultMapper(mapper);
		if(queryCommond.getPage()==-1)
		{
			questionnaireBeanList = (List<QueryQuestionnaireBean>) this.findList(queryCondition);
		}
		else
		{
			int firstResult = queryCommond.calculate(count.longValue());
			questionnaireBeanList = (List<QueryQuestionnaireBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return questionnaireBeanList;
	}
	/**
	 * 查看参与问卷调查的人员详情
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-4-29
	 */
	@Override
	public List<QuerySurveyObjectBean> findSurveyUserInfo(QuestionnaireSurveyObjectQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("SELECT bu.USERID as userId,bu.user_name as userName,bb_1.DATA_NAME as sex,bu.birthday as birthday," +
				" bd.DEPNAME as depName,bu.DUTY_REMARK as position,bb.DATA_NAME as rank,bu.login_id as loginId," +
				" qso.QUESTIONNAIRE_ID as  questionnaireId" +
				" FROM questionnaire_survey_object qso " +
				" RIGHT JOIN base_user bu ON bu.USERID=qso.USERID" +
				" LEFT JOIN base_basedata bb ON bu.DUTY_LEVEL_CODE=bb.DATA_CODE" +
				" LEFT JOIN base_basedata bb_1 ON bu.sex_code=bb_1.DATA_CODE" +
				" LEFT JOIN base_department_user bdu ON bu.USERID=bdu.USERID" +
				" LEFT JOIN base_department bd ON bdu.DEPID=bd.DEPID" +
				" WHERE qso.QUESTIONNAIRE_ID = :questionnaireId");
		params.put("questionnaireId", queryCommond.getQuestionnaireId());
		if(PropertyUtil.objectNotEmpty(queryCommond.getUserName()))
		{
			String userName=SqlQueryUtil.changeString(queryCommond.getUserName().replace(" ", ""));
			sql.append(" and bu.user_name like :userName");
			params.put("userName", userName);
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getDepName()))
		{
			sql.append(" and bd.DEPNAME like :depName");
			params.put("depName", "%"+queryCommond.getDepName().trim()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getRankCode()))
		{
			sql.append(" and bu.DUTY_LEVEL_CODE = :rankCode");
			params.put("rankCode", queryCommond.getRankCode());
		}
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QuerySurveyObjectBean> resultList=new ArrayList<QuerySurveyObjectBean>();
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QuerySurveyObjectBean.class);
		queryCondition.setResultMapper(mapper);
		if(queryCommond.getPage()==-1)
		{
			resultList = (List<QuerySurveyObjectBean>) this.findList(queryCondition);
		}
		else
		{
			int firstResult = queryCommond.calculate(count.longValue());
			resultList = (List<QuerySurveyObjectBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		
		return resultList;
	}
	/**
	 * 查看已结束问卷试题调查情况
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-4-29
	 */
	@Override
	public List<QueryQuestionBankBean> endQuestionnaireResult(QuestionBankQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("select * from (");
		sql.append("SELECT bu.user_name as userName,bd.DEPNAME as depName,qb.QUESTION_NAME as questionName," +
				" qb.QUESTION_ID as questionId,qua.QUESTION_ANSWER as questionAnswer,qq.ORDER_NUM as orderNum" +
				" from questionnaire_user_answer qua " +
				" LEFT JOIN question_bank qb ON qua.QUESTION_ID=qb.QUESTION_ID" +
				" LEFT JOIN questionnaire_question qq ON qb.QUESTION_ID=qq.QUESTION_ID" +
				" LEFT JOIN base_user bu ON qua.USERID=bu.USERID" +
				" LEFT JOIN base_department_user bdu ON bu.USERID=bdu.USERID" +
				" LEFT JOIN base_department bd ON bdu.DEPID=bd.DEPID" +
				" where 1=1");
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionnaireId()))
		{
			sql.append(" and qua.QUESTIONNAIRE_ID = '"+queryCommond.getQuestionnaireId()+"'");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getQuestionType()))
		{
			sql.append(" and qb.QUESTION_TYPE IN ("+queryCommond.getQuestionType()+")");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getOrderNum()))
		{
			sql.append(" and qq.ORDER_NUM="+queryCommond.getOrderNum());
		}
		sql.append(") as t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		IConditionBuilder defaultQueryConditionBuilder=new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryQuestionBankBean.class);
		queryCondition.setResultMapper(mapper);
		
		List<QueryQuestionBankBean> resultList=new ArrayList<QueryQuestionBankBean>();
		if(queryCommond.getPage()==-1)
		{
			resultList = (List<QueryQuestionBankBean>) this.findList(queryCondition);
		}
		else
		{
			int firstResult = queryCommond.calculate(count.longValue());
			resultList = (List<QueryQuestionBankBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	/**
	 * 预览问卷
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-28
	 */
	@Override
	public List<QueryQuestionBankBean> queryQuestionnaireQuestion(QuestionnaireValidCommond commond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		sql.append("select qb.QUESTION_ID as questionId,qb.QUESTION_NAME as questionName,qq.ORDER_NUM as orderNum," +
				" GROUP_CONCAT(CONCAT(qbo.OPTION_NUM,'-',qbo.OPTION_NAME)) as questionOptionStr,qb.QUESTION_TYPE as questionType");
		if(PropertyUtil.objectNotEmpty(commond.getUser())&&PropertyUtil.objectNotEmpty(commond.getUser().getUserId()))
		{
			sql.append(",(select qua.QUESTION_ANSWER from questionnaire_user_answer qua where qua.QUESTIONNAIRE_ID=qq.QUESTIONNAIRE_ID " +
					" and qua.QUESTION_ID=qq.QUESTION_ID and qua.USERID = '"+commond.getUser().getUserId()+"') as QUESTION_ANSWER," +
					"(select qua.QUESTION_OPTION from questionnaire_user_answer qua where qua.QUESTIONNAIRE_ID=qq.QUESTIONNAIRE_ID " +
					" and qua.QUESTION_ID=qq.QUESTION_ID and qua.USERID = '"+commond.getUser().getUserId()+"') as QUESTION_OPTION");
		}
		sql.append(" from questionnaire_question qq " +
				" INNER JOIN question_bank qb ON qq.QUESTION_ID=qb.QUESTION_ID" +
				" LEFT JOIN question_bank_option qbo ON qq.QUESTION_ID=qbo.QUESTION_ID" +
				" LEFT JOIN base_basedata bb ON qb.QUESTION_TYPE=bb.DATA_CODE" +
				" WHERE qq.QUESTIONNAIRE_ID = '"+commond.getQuestionnaireId()+"'" +
				" GROUP BY qb.QUESTION_ID ORDER BY bb.ORDER_NUM,qq.ORDER_NUM");
		
//		IConditionBuilder defaultQueryConditionBuilder=new DefaultQueryConditionBuilder();
//		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
//		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
//		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
//		queryCondition.setParameters(params);
//		
//		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
//		mapper.setResultClass(QueryQuestionBankBean.class);
//		queryCondition.setResultMapper(mapper);
//		
//		List<QueryQuestionBankBean> resultList=(List<QueryQuestionBankBean>) this.findList(queryCondition);
		
		List<QueryQuestionBankBean> resultList=new ArrayList<QueryQuestionBankBean>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try
		{
			conn=this.getSession().connection();
			ps=conn.prepareStatement(sql.toString());
			
			rs=ps.executeQuery();
			while(rs.next())
			{
				QueryQuestionBankBean bean=new QueryQuestionBankBean();
				bean.setQuestionId(rs.getString("questionId"));
				bean.setQuestionName(rs.getString("questionName"));
				bean.setOrderNum(rs.getInt("orderNum"));
				bean.setQuestionOptionStr(rs.getString("questionOptionStr"));
				bean.setQuestionType(rs.getString("questionType"));
				
				if(rs.getString("QUESTION_ANSWER")!=null)
					bean.setQuestionAnswer(rs.getString("QUESTION_ANSWER"));
				else
					bean.setQuestionAnswer(rs.getString("QUESTION_OPTION"));
				
				resultList.add(bean);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if(rs!=null)
				rs.close();
			if(ps!=null)
				ps.close();
			if(conn!=null)
				conn.close();
		}
		return resultList;
	}
	/**
	 * 删除问卷试题关联
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-28
	 */
	@Override
	public void delQuestionnaireQuestion(QuestionBankQueryCommond queryCommond) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("delete from questionnaire_question where questionnaire_question.QUESTION_ID in (:questionIds)");
		params.put("questionIds", queryCommond.getQuestionIds());
		
		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}
	/**
	 * 删除问卷试题后更新试题排序
	 * @param questionnaireQuestionIdList
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-09-12
	 */
	@Override
	public void sortQuestionAfterDel(List<String> questionnaireQuestionIdList) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("update questionnaire_question qq set qq.ORDER_NUM=case qq.QUESTIONNAIRE_QUESTION_ID");
		for(int i=1;i<=questionnaireQuestionIdList.size();i++)
		{
			sql.append(" when '"+questionnaireQuestionIdList.get(i-1)+"' then "+i);
		}
		sql.append(" else qq.ORDER_NUM end");
		sql.append(" where qq.QUESTIONNAIRE_QUESTION_ID in (:questionnaireQuestionIdList)");
		params.put("questionnaireQuestionIdList", questionnaireQuestionIdList);
		
		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}
	/**
	 * 批量导入Excel试题到题库
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-11-11
	 */
	@Override
	public boolean addBatchQuestion(final List<QuestionBankValidCommond> commondList) throws Exception
	{
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>()
		{
			@Override
			public Boolean doInConnection(Connection con) throws SQLException,
					DataAccessException
			{
				PreparedStatement pstmt=null;
				try
				{
					String sql="insert into question_bank(QUESTION_ID,QUESTION_TYPE,QUESTION_NAME,USERID,CREATE_DATE,ACTIVE_STATE,WORD_NUM)" +
							" values(?,?,?,?,?,?,?)";
					pstmt=con.prepareStatement(sql);
					
					for(QuestionBankValidCommond commond: commondList)
					{
						pstmt.setString(1, commond.getQuestionId());
						pstmt.setString(2, commond.getQuestionType());
						pstmt.setString(3, commond.getQuestionName());
						pstmt.setString(4, commond.getUser().getUserId());
						pstmt.setString(5, DateUtil.getDateString(new Date(), "yyyy-MM-dd HH:mm:ss"));
						pstmt.setInt(6, QuestionBank.IS_ACTIVE_Y);
						if(PropertyUtil.objectNotEmpty(commond.getWordNum()))
							pstmt.setInt(7, commond.getWordNum());
						else
							pstmt.setNull(7, Types.INTEGER);
						
						pstmt.addBatch();
					}
					pstmt.executeBatch();
					return true;
				}
				catch(Exception e)
				{
					e.printStackTrace();
					return false;
				}
				finally
				{
					if(pstmt!=null)
						pstmt.close();
				}
			}
		});
	}
	/**
	 * 批量导入Excel试题选项到题库
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-11-11
	 */
	@Override
	public boolean addBatchQuestionOptions(final List<QuestionBankValidCommond> commondList) throws Exception
	{
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>()
		{
			@Override
			public Boolean doInConnection(Connection con) throws SQLException,
			DataAccessException
			{
				PreparedStatement pstmt=null;
				try
				{
					String sql="insert into question_bank_option(OPTION_ID,QUESTION_ID,OPTION_NAME,OPTION_NUM,CREATE_DATE,ACTIVE_STATE)" +
							" values(?,?,?,?,?,?)";
					pstmt=con.prepareStatement(sql);
					
					for(QuestionBankValidCommond commond: commondList)
					{
						String[] options=commond.getOptions();
						for(int i=0;i<options.length;i++)
						{
							if(PropertyUtil.objectNotEmpty(options[i]))
							{
								pstmt.setString(1, UUID.randomUUID().toString().replace("-", ""));
								pstmt.setString(2, commond.getQuestionId());
								pstmt.setString(3, options[i]);
								pstmt.setString(4, String.valueOf((char)(i+(int)('A'))));
								pstmt.setString(5, DateUtil.getDateString(new Date(), "yyyy-MM-dd HH:mm:ss"));
								pstmt.setInt(6, QuestionBank.IS_ACTIVE_Y);
								
								pstmt.addBatch();
							}
						}
					}
					pstmt.executeBatch();
					return true;
				}
				catch(Exception e)
				{
					e.printStackTrace();
					return false;
				}
				finally
				{
					if(pstmt!=null)
						pstmt.close();
				}
			}
			
		});
	}
	/**
	 * 批量查询题库重复试题
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-11-11
	 */
	public Map<String, List<String>> queryRepeatNameQuestion(Map<String, List<String>> typeNameMap) throws Exception
	{
		StringBuffer sql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		sql.append("select qb.QUESTION_TYPE,qb.QUESTION_NAME from question_bank qb where qb.ACTIVE_STATE="+QuestionBank.IS_ACTIVE_Y);
	
		if(PropertyUtil.objectNotEmpty(typeNameMap))
		{
			String querySql="";
			int i=0;
			for(Map.Entry<String, List<String>> map: typeNameMap.entrySet())
			{
				querySql+=" (qb.QUESTION_TYPE = :questionType"+i+" and qb.QUESTION_NAME in (:questionName"+i+")) or";
				params.put("questionType"+i, map.getKey());
				params.put("questionName"+i, map.getValue());
				i++;
			}
			sql.append(" and ("+querySql.substring(0,querySql.length()-2)+")");
		}
		
		typeNameMap=new HashMap<String, List<String>>();
		List<Object[]> list=(List<Object[]>) this.executeFind(QueryStringType.SQL, sql.toString(), params);
		for(Object[] obj: list)
		{
			if(!typeNameMap.containsKey(obj[0].toString()))
			{
				typeNameMap.put(obj[0].toString(), new ArrayList<String>());
			}
			typeNameMap.get(obj[0].toString()).add(obj[1].toString());
		}
		return typeNameMap;
	}
	/**
	 * @author RongSL
	 * @Date 2016-05-26
	 * 根据课程id找到相关班级id
	 * */
	@Override
	public String findClassId(QuestionnaireSurveyObjectValidCommond commond) throws Exception{
		String sql="select tc.class_id from training_class tc left join training_class_course tcc on tc.class_id=tcc.class_id " +
				"where tc.class_id=:id or tcc.class_course_id=:id ";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("id", commond.getSurveyObjectId());
		return (String) this.executeFind(QueryStringType.SQL, sql, params).get(0);
	}
}
